﻿create view view_workitem
as
select t1.id,
       t1.flowobjectid,
       t2.forminstanceid,
       t2.dataid,
       t2.description,
       t2.state as FlowState,
       t3.name as FlowName,
       t3.description as FlowDescription,
       t4.username as UserName,
       t5.Name as Dept,
       to_char(t2.creationtime, 'yyyy 年 MM 月 dd 日') as CreationTime,
       t1.workuserid,
       t1.state,
       to_char(t1.updatedtime, 'yyyy 年 MM 月 dd 日') as UpdatedTime,
       result
from wf_taskobject t1 
   inner join wf_flowobject t2 on t1.flowobjectid = t2.id
   inner join wf_workflow       t3 on t2.flowid = t3.id
   inner join sys_userinfo  t4 on t2.createuserid = t4.userid
   inner join sys_org       t5 on t2.createdeptid = t5.id;


create view view_workuser
as
select t1.id,
       t1.flowobjectid,
       t1.taskname,
       t2.username,
       t1.state,
       to_char(t1.updatedtime, 'yyyy 年 MM 月 dd 日') as updatedtime,
       t1.result
from wf_taskobject t1
   inner join sys_userinfo t2 on t1.workuserid = t2.userid
order by id;


--create view view_userorgposition
--as
--select t1.*, t2.username, t3.name org, t4.name "position", t5.username parent from sys_userorgposition t1 
--	inner join sys_userinfo t2 on t1.userid = t2.userid
--	inner join sys_org t3 on t1.orgid = t3.id
--	inner join sys_position t4 on t1.positionid = t4.id
--	left join sys_userinfo t5 on t1.parentuserid = t5.userid;

--create view view_userrole
--as
--select t1.*, t2.username, t3.name "role" from sys_userrole t1
--	inner join sys_userinfo t2 on t1.userid = t2.userid
--	inner join sys_role t3 on t1.roleid = t3.id;


create view view_flowobject
as
select t1.*,
       t2.name as flowname,
       t2.description as flowdescription,
       t3.username,
       t4.name as dept
from wf_flowobject t1
   inner join wf_workflow t2 on t1.flowid = t2.id
   inner join sys_userinfo t3 on t1.createuserid = t3.userid
   inner join sys_org t4 on t1.createdeptid = t4.id;


create or replace function check_userrole(_user int, _roles int[])
returns boolean
as
$$
select case count(*) when 0 then false else true end as result
   from sys_userrole
   where userid = _user and roleid in (select unnest(_roles));
$$
language sql;

create or replace function check_userrole(_user int, _role int)
returns boolean
as
$$
select case count(*) when 0 then false else true end as result
   from sys_userrole
   where userid = _user and roleid = _role;
$$
language sql;




create or replace function check_form_data(_flowobject int, _data int, _field int, _value anyelement)
returns boolean
as
$$
declare
   _r1 record;
   _result boolean;
begin
   _result := false;
   select * into _r1 from wf_flowobject where id = _flowobject;
   execute 
	'select 
	    case count(*) when 0 then false else true end
         from workflow.form_' || _r1.forminstanceid::text || ' 
         where id = $1 and field_'|| _field::text || ' = $2;' 
   into _result
   using _data, _value;
   return _result;
end;
$$
language plpgsql;


create or replace function check_form_data_where(_flowobject int, _data int, _field int, _where text)
returns boolean
as
$$
declare
   _r1 record;
   _result boolean;
begin
   _result := false;
   select * into _r1 from wf_flowobject where id = _flowobject;
   execute 
	'select 
	    case count(*) when 0 then false else true end
         from workflow.form_' || _r1.forminstanceid::text || ' 
         where id = $1 and field_'|| _field::text || ' ' || _where
   into _result
   using _data;
   return _result;
end;
$$
language plpgsql;



--create or replace function get_path(_flow int, _task int)
--returns table(flowid int, fromtaskid int, totaskidsuccess int, totaskidfailed int, taskname text, sqlcondition text)
--as
--$$
--begin
--  return query select t.flowid, t.fromtaskid, t.totaskidsuccess, t.totaskidfailed, t.taskname, t.sqlcondition 
--	       from wf_path t
--	       where t.flowid = _flow and t.fromtaskid = _task and t.state = 1 
--	       order by t.id;
--end;
--$$
--language plpgsql;